package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import auxilary.Log;
import entities.Course;
import entities.User;
import exceptions.DBException;
import exceptions.MyException;
import exceptions.UserException;

/* ******************************************************** */
/* 							UserHandler						*/
/*															*/
/*	Takes care of communicating with the database on		*/
/* 	behalf of the user										*/
/*															*/
/* ******************************************************** */

public class UserHandler extends DBHandler {

	/*
	 * Add User - add user to database (returns userId)
	 */
	public int addUser(User user) throws MyException {
		// check user fields
		if (user == null || user.getPassword() == null
				|| user.getPassword().isEmpty() || user.getUsername() == null
				|| user.getUsername().isEmpty())
			throw new MyException(UserException.BAD_PARAMS);

		// insert into the database
		Object[][] data = this.createAddUserData(user);
		int id = 0;
		try {
			id = super.insert(data, Tables.SCHEMA + "." + Tables.User.dbName, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return id;
	}

//	/*
//	 * usernameAvailable - check if username is available
//	 */
//	public boolean usernameAvailable(String username) throws MyException {
//		User user = null;
//		try {
//			user = this.fetchUser(username);
//		} catch (MyException e) {
//			switch (e.getErrorCode()) {
//				case DBException.VALUE_ALREADY_EXISTS :
//					return false;
//				case DBException.VALUE_NOT_FOUND :
//					return true;
//				default :
//					throw e;
//			}
//		}
//		return (user == null);
//	}

	/*
	 * Authenticate - authenticate user by username and password. if
	 * authenticated returns user.
	 */
	public User Authenticate(String username, String password)
			throws MyException {
		if (username == null || username.isEmpty() || password == null
				|| password.isEmpty())
			throw new MyException(UserException.BAD_PARAMS);

		// get the user from the DB.
		String where = Tables.User.Columns.USERNAME + "=\"" + username
				+ "\" and " + Tables.User.Columns.PASSWORD + "=\"" + password
				+ "\"";
		return this.doFetchUser(where);
	}

	/*
	 * Fetch User - get user by id from the db
	 */
	public User fetchUser(int id) throws MyException {
		if (id < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the user from the DB.
		String where = Tables.User.Columns.ID + "=" + id;
		return this.doFetchUser(where);
	}

	/*
	 * Fetch User - get user by username from the db
	 */
	public User fetchUser(String username) throws MyException {
		// check username
		if (username == null || username.isEmpty())
			throw new MyException(UserException.BAD_PARAMS);

		// get the user from the DB.
		String where = Tables.User.Columns.USERNAME + "=" + "\"" + username
				+ "\"";
		return this.doFetchUser(where);
	}

	/*
	 * Fetch All Users - get all users from the db
	 */
	public List<User> fetchAllUsers() throws MyException {
		// get the user from the DB.
		ResultSet rs = null;
		List<User> users = null;
		try {
			rs = getUserResultSet(null);
			users = buildUsersList(rs);
		} finally {
			closeConnection(rs);
		}
		return users;
	}

	/*
	 * Update Field - update the user field to newValue in the db
	 */
	public User updateField(User user, String field, String newValue)
			throws MyException {
		if (user == null || user.getId() < 0)
			throw new MyException(UserException.BAD_PARAMS);
		String where = Tables.User.Columns.ID + "=" + user.getId();
		Log.dbg(where);

		ResultSet rs = null;
		try {
			rs = this.getUserResultSet(where);
			User updatedUser = null;
			try {
				rs.first();
				// update values
				rs.updateString(field, newValue);
				rs.updateRow();
				updatedUser = this.resultSetToUser(rs, false, false);
			} catch (SQLException e) {
				throw new DBException(Tables.User.dbName, null, e.getMessage(),
						DBException.DEFAULT);
			}
			return updatedUser;
		} finally {
			closeConnection(rs);
		}
	}

	/*
	 * Delete User - deletes user from db
	 */
	public void deleteUser(int id) throws MyException {
		// check id
		if (id < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// delete all courses belonging to user
		CourseHandler ch = new CourseHandler();
		try {
			List<Course> courses = ch.fetchAllCourses(id);
			if (courses != null) {
				for (Course course : courses) {
					try {
						ch.deleteCourse(course.getId());	
					} catch(Exception e) {
						// ignore - may be caused by multiple course delete
					}
				}
			}
		} catch (Exception e) {
			// ignore - may be caused by multiple course delete
		}
		
		// delete all user2course entrences
		String where = new String(Tables.CourseToUser.Columns.USER_ID + " = "
				+ id);
		ResultSet rs = this.getCourseToUserResultSet(where);
		if (rs != null) {
			try {
				rs.first();
				rs.deleteRow();
				while (rs.next()) {
					rs.deleteRow();
				}
				this.closeConnection(rs);
			} catch (SQLException e) {
				throw new DBException(Tables.User.dbName, "DELETE USER",
						e.getMessage(), DBException.DEFAULT);
			} finally {
				this.closeConnection(rs);
			}
		}

		// get user
		where = new String(Tables.User.Columns.ID + " = " + id);
		rs = this.getUserResultSet(where);
		if (this.isEmptyResultSet(rs, Tables.User.dbName)) {
			this.closeConnection(rs);
			throw new DBException(Tables.User.dbName, null, "Value not found",
					DBException.VALUE_NOT_FOUND);
		}

		// delete user
		try {
			rs.first();
			rs.deleteRow();
			this.closeConnection(rs);
		} catch (SQLException e) {
			throw new DBException(Tables.User.dbName, "DELETE USER",
					e.getMessage(), DBException.DEFAULT);
		} finally {
			this.closeConnection(rs);
		}
	}

	/* ******* */
	/* Helpers */
	/* ******* */

	/*
	 * Do Fetch User - do the fetching of the user (condition: where)
	 */
	private User doFetchUser(String where) throws DBException {
		ResultSet rs = null;
		User user = null;
		try {
			rs = getUserResultSet(where);
			user = resultSetToUser(rs, false, true);
		} catch (Exception e) {
			return null;
		} finally {
			closeConnection(rs);
		}
		return user;
	}

	/*
	 * Create Add User Data - create user data array that is passed to the
	 * insert method
	 */
	private Object[][] createAddUserData(User user) {
		Object[][] data = new Object[9][2];
		// create data array
		data[0][COL_NAME] = Tables.User.Columns.PASSWORD;
		data[0][COL_VAL] = user.getPassword();
		data[1][COL_NAME] = Tables.User.Columns.FIRST_NAME;
		data[1][COL_VAL] = user.getFirstName();
		data[2][COL_NAME] = Tables.User.Columns.LAST_NAME;
		data[2][COL_VAL] = user.getLastName();
		data[3][COL_NAME] = Tables.User.Columns.EMAIL;
		data[3][COL_VAL] = user.getEmail();
		data[4][COL_NAME] = Tables.User.Columns.USERNAME;
		data[4][COL_VAL] = user.getUsername();
		data[5][COL_NAME] = Tables.User.Columns.PHONE;
		data[5][COL_VAL] = user.getPhone();
		data[6][COL_NAME] = Tables.User.Columns.FACULTY;
		data[6][COL_VAL] = user.getFaculty();
		data[7][COL_NAME] = Tables.User.Columns.SEMESTER;
		data[7][COL_VAL] = user.getSemester();
		data[8][COL_NAME] = Tables.User.Columns.IS_ADMIN;
		data[8][COL_VAL] = user.isAdmin() ? "1" : "0";
		return data;
	}

	/*
	 * Get User ResultSet - fetch user from the DB and return it's ResultSet
	 */
	private ResultSet getUserResultSet(String where) throws DBException {
		ResultSet rs = null;
		try {
			rs = super.fetch(Tables.SCHEMA + "." + Tables.User.dbName,
					where, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (this.isEmptyResultSet(rs, Tables.SCHEMA + "." + Tables.User.dbName)) {
			this.closeConnection(rs);
			throw new DBException(Tables.User.dbName, null, "Value not found",
					DBException.VALUE_NOT_FOUND);
		}
		return rs;
	}

	/*
	 * Get User ResultSet - fetch user from the DB and return it's ResultSet
	 */
	public ResultSet getCourseToUserResultSet(String where) throws DBException {
		ResultSet rs = null;
		try {
			rs = super.fetch(Tables.SCHEMA + "."
					+ Tables.CourseToUser.dbName, where, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (this.isEmptyResultSet(rs, Tables.SCHEMA + "." + Tables.User.dbName)) {
			this.closeConnection(rs);
			return null;
		}
		return rs;
	}

	/*
	 * ResultSet to User - convert ResultSet to User
	 */
	private User resultSetToUser(ResultSet rs, boolean isClose,
			boolean isNeedNext) throws DBException {
		// convert result to User type and close connection
		User user = null;
		try {
			if (isNeedNext)
				rs.next();
			user = new User(rs.getInt(Tables.User.Columns.ID),
					rs.getString(Tables.User.Columns.PASSWORD),
					rs.getString(Tables.User.Columns.USERNAME),
					rs.getString(Tables.User.Columns.FIRST_NAME),
					rs.getString(Tables.User.Columns.LAST_NAME),
					rs.getString(Tables.User.Columns.EMAIL),
					rs.getString(Tables.User.Columns.FACULTY),
					rs.getInt(Tables.User.Columns.SEMESTER),
					rs.getString(Tables.User.Columns.PHONE),
					rs.getBoolean(Tables.User.Columns.IS_ADMIN));

		} catch (SQLException e) {
			throw new DBException(Tables.User.dbName, null, e.getMessage(),
					DBException.CLOSE);
		} finally {
			if (isClose)
				this.closeConnection(rs);
		}
		return user;
	}

	/*
	 * Build Users List - build users list from ResultSet
	 */
	private List<User> buildUsersList(ResultSet rs) throws DBException {
		ArrayList<User> users = new ArrayList<User>();

		// extract all items from ResultSet
		try {
			rs.first();
			while (!rs.isAfterLast()) {
				users.add(this.resultSetToUser(rs, false, false));
				rs.next();
			}
		} catch (SQLException e) {
			throw new DBException(Tables.User.dbName, "ON BUILD USERS ARRAY",
					e.getMessage(), DBException.DEFAULT);
		} finally {
			this.closeConnection(rs);
		}
		return users;
	}
}
